﻿--ref :死锁全面解析 http://www.simple-talk.com/sql/database-administration/handling-deadlocks-in-sql-server/
--ref :msdn死锁dm函数 http://technet.microsoft.com/en-us/library/ms186327.aspx
--初始化
-- Sample Table, which will track individual Orders entered by 
-- Various Sales Persons
-- 1) Create Objects for Deadlock 
SET NOCOUNT ON
DBCC TRACEON (1222, -1)

CREATE TABLE dbo.student (stuid INT, stuname varchar(20), claid int)
INSERT student(stuid, stuname, claid) values(1, 'john', 1)
INSERT student(stuid, stuname, claid) values(2, 'Kate', 1)

CREATE TABLE dbo.class (claid INT)
INSERT dbo.class SELECT 1
INSERT dbo.class SELECT 2

drop table student
drop table class

--产生死锁
-- 2) Q1
BEGIN TRAN
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
select * from dbo.student 
-- 3) Q2
BEGIN TRAN
UPDATE dbo.student SET claid=2 where stuid = 2 
-- 4) Q1 again
update dbo.student set claid=1 from dbo.student where stuid = 1


--检测死锁
--* 阻塞检测
select sysobjects.name , sys.dm_tran_locks.* 
from sys.dm_tran_locks left join sysobjects on sys.dm_tran_locks.resource_associated_entity_id = sysobjects.id

--* 检测死锁数
select * 
from master.dbo.sysperfinfo
where object_name = 'SQLServer:Locks' and counter_name = 'Number of Deadlocks/sec' and 
	instance_name = '_Total'
----------------------------------------------------------------问题
---q1
SET NOCOUNT ON
DBCC TRACEON (1222,3604, -1)
BEGIN TRAN

--select * from dbo.student  where claid = 1

update dbo.student set stuname='wang' from dbo.student where claid = 1

--UPDATE dbo.student SET claid = 2 from dbo.student  where claid = 2

--rollback tran



---q2
SET NOCOUNT ON
DBCC TRACEON (1222,3604, -1)
BEGIN TRAN
UPDATE dbo.student SET claid = 3 from dbo.student  where claid = 3

--select * from student where claid = 3
--rollback tran
----------------------------------------------------------------问题



---
--预防死锁
--* 尽可能让事务轻薄短小、让锁定的时间尽量短.
--* 游标中尽量少用更新, 因为其运行时间长, 会长时间占用锁
--* 若无必要，使用较宽松的事务隔离级别，如前述的 WITH (NOLOCK) 和 READ UNCOMMITTED。
--读使用nolock会避免死锁但有副作用
--* 避免在事务执行期间，还要等待用户的反馈或交互.
--* 尽量使用临时表, 好处很多
--* 避免在查询时 JOIN 过多的表 (此指非必要的 JOIN)，否则除了性能较差外，也很容易读到正被锁定或阻塞中的表和字段。
--* 应注意在一个没有索引的表上，过量的「行锁」，或一些锁定使用了过多的内存和系统资源时，
--SQL Server 为了有效地管理这些锁定，会尝试将锁定扩展为整个表的「表锁」，
--此时会很容易造成其他 process 在访问时的阻塞和等待。
--* bookmark 查找导致
--* 避免全表扫描